<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Print_disconnect_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }


   public function get_arrears_dis($area){
     $sql;
    if($area == 'ALL'){
      $sql = ""; 
    }else{
      $sql = " AND aea.zone_1 = '".$area."' "; 
    }
    $q = $this->db->query("SELECT tbl.area,tbl.name,tbl.num,tbl.a_count FROM (
                    SELECT aea.zone_1 area
                      ,aa.acct_num num
                      ,aa.acct_name name
                        ,count(x.customer_id) a_count
                    FROM ar_account aa
                    INNER JOIN ar_entity_address aea
                          ON aa.acct_addr = aea.address_id
                    LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)) x ON x.customer_id = aa.account_id
                    WHERE aa.enable_flag = 'Y' and aa.status = 'ACT'
                      ".$sql."
                    AND LAST_DAY(aa.active_start)
                      BETWEEN LAST_DAY(aa.active_start)
                      AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
                      GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 
                  ) tbl
                  WHERE a_count > 1
                  GROUP BY area,name,num
                  ORDER BY tbl.a_count ASC");
    return $q->result();
     }


   public function get_zones(){
   $sql = "SELECT * FROM app_lookup_values WHERE lookup_type = 'LUMBIA_AREAS' ";

   $query = $this->db->query($sql);
   return $query->result();
  }

  public function get_cons($mon,$yr,$zone){
    $sql= "SELECT umr.* , aa.acct_name, aa.acct_num FROM ubs_meter_readings umr
    INNER JOIN ar_account aa on aa.account_id = umr.account_id
    INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
        WHERE umr.bill_month = ".$mon." AND umr.bill_year = ".$yr." AND aea.zone_1 = '".$zone."'
        ";

        $query = $this->db->query($sql);
    return $query->result();
  }

   
}